package com.eastcom.collection.tools.db

/**
 * Created by yuanyue on 2014-08-05.
 */
import java.sql.Connection;
import java.sql.DriverManager
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DerByUtil {

    static String EMBEDED_DRIVER = "org.apache.derby.jdbc.EmbeddedDriver";
    static String NET_DRIVER = "org.apache.derby.jdbc.ClientDriver";

    static String DB_NAME = "E:\\task_config";
    static String HOST_URL = "//localhost:1527/";

    /**
     * @param args
     */
    public static void main(String[] args) {
        // TODO Auto-generated method stub
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = getNetwrokConnect(HOST_URL+DB_NAME);
            stmt = conn.createStatement();
//            stmt.executeUpdate("drop table TASK_CONFIG")
//            stmt.executeUpdate("create table TASK_CONFIG(\n" +
//                    "  task_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,\n" +
//                    "  task_name VARCHAR(24) NOT NULL\n" +
//                    ")");
//            while (rs != null && rs.next()) {
//                System.out.println(rs.getString(2));
//            }
//            stmt.execute("ALTER TABLE TASK_CONFIG  ADD addfield1 varchar(24)")
//            stmt.executeUpdate("insert into TASK_CONFIG values(1,'222','333')")
//            String sql = "select a.tablename,b.columnname from SYS.SYSTABLES a join SYS.SYSCOLUMNS b on referenceid = tableid\n" +
//                "where tablename = '"+TASK_CONFIG+"'"
//            rs = stmt.executeQuery("select * from TASK_CONFIG")
//            while (rs != null && rs.next()) {
//                System.out.println(rs.getString(2));
//            }
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            rs = null;
            try {
                stmt.close();
            } catch (Exception e) {
            }
            stmt = null;
            try {
                conn.close();
            } catch (Exception e) {
            }
            conn = null;
        }
    }

    public static List<String> getConfigTableInfo(String tableName){
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        List<String> columnList = new ArrayList<>()
        try {
            conn = getNetwrokConnect(HOST_URL+DB_NAME);
            String sql = "select b.columnname from SYS.SYSTABLES a join SYS.SYSCOLUMNS b on referenceid = tableid\n" +
                    "where tablename = '"+tableName+"'"
            PreparedStatement pst = conn.prepareStatement(sql,
                    ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            rs = pst.executeQuery()
            while (rs != null && rs.next()) {
                int columnNum = rs.getMetaData().getColumnCount()
                for(int i = 1; i <= columnNum; i ++){
                    columnList.add(rs.getString(i))
                    System.out.println(rs.getString(i));
                }
            }
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            rs = null;
            try {
                stmt.close();
            } catch (Exception e) {
            }
            stmt = null;
            try {
                conn.close();
            } catch (Exception e) {
            }
            conn = null;
        }
        return columnList
    }
    public static Connection getEmbededConnect(String dbName) {
        try {
            Class.forName(EMBEDED_DRIVER);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            throw new RuntimeException(e.getMessage());
        }
        try {
            return DriverManager.getConnection("jdbc:derby:" + dbName+";create=true");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            throw new RuntimeException(e.getMessage());
        }
    }

    public static Connection getNetwrokConnect(String url) {
        try {
            Class.forName(NET_DRIVER);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            throw new RuntimeException(e.getMessage());
        }
        try {
            return DriverManager.getConnection("jdbc:derby:"
                    + url+";create=true");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            throw new RuntimeException(e.getMessage());
        }
    }

}

